package org.realogic.cfm.persistence.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.realogic.cfm.domain.KPageResult;
import org.realogic.cfm.exception.KSystemErrorCode;
import org.realogic.cfm.exception.KSystemException;
import org.realogic.cfm.persistence.KParameter;
import org.realogic.cfm.persistence.dialect.Dialect;
import org.realogic.cfm.persistence.query.QueryCondition;

public final class KJdbcDAOUtil {

	public static final int FETCH_SIZE = 100;

	public static String findAsString(Connection theConn, String theSQL, KParameter... theParams) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String result = null;
		try {
			pstmt = theConn.prepareStatement(theSQL, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			if (theParams != null) {
				// 设置查询参数
				for (int i = 0; i < theParams.length; i++) {
					theParams[i].fillPreparedStatement(pstmt, i + 1);
				}
			}
			// 执行查询
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result = rs.getString(1);
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			closeResultSet(rs);
			closeStatement(pstmt);
		}
		return result;
	}

	public static String findAsString(Connection theConn, String theSQL, List<KParameter> theParams) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String result = null;
		try {
			pstmt = theConn.prepareStatement(theSQL, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			if (theParams != null) {
				// 设置查询参数
				int size = theParams.size();
				for (int i = 0; i < size; i++) {
					theParams.get(i).fillPreparedStatement(pstmt, i + 1);
				}
			}
			// 执行查询
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result = rs.getString(1);
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			closeResultSet(rs);
			closeStatement(pstmt);
		}
		return result;
	}

	public static int findAsInt(Connection theConn, String theSQL, KParameter... theParams) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int result = Integer.MIN_VALUE;
		try {
			pstmt = theConn.prepareStatement(theSQL, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			if (theParams != null) {
				// 设置查询参数
				for (int i = 0; i < theParams.length; i++) {
					theParams[i].fillPreparedStatement(pstmt, i + 1);
				}
			}
			// 执行查询
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result = rs.getInt(1);
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			closeResultSet(rs);
			closeStatement(pstmt);
		}
		return result;
	}

	public static int findAsInt(Connection theConn, String theSQL, List<KParameter> theParams) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int result = Integer.MIN_VALUE;
		try {
			pstmt = theConn.prepareStatement(theSQL, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			if (theParams != null) {
				// 设置查询参数
				int size = theParams.size();
				for (int i = 0; i < size; i++) {
					theParams.get(i).fillPreparedStatement(pstmt, i + 1);
				}
			}
			// 执行查询
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result = rs.getInt(1);
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			closeResultSet(rs);
			closeStatement(pstmt);
		}
		return result;
	}

	public static double findAsDouble(Connection theConn, String theSQL, KParameter... theParams) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		double result = Double.MIN_VALUE;
		try {
			pstmt = theConn.prepareStatement(theSQL, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			if (theParams != null) {
				// 设置查询参数
				for (int i = 0; i < theParams.length; i++) {
					theParams[i].fillPreparedStatement(pstmt, i + 1);
				}
			}
			// 执行查询
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result = rs.getDouble(1);
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			closeResultSet(rs);
			closeStatement(pstmt);
		}
		return result;
	}

	public static double findAsDouble(Connection theConn, String theSQL, List<KParameter> theParams) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		double result = Double.MIN_VALUE;
		try {
			pstmt = theConn.prepareStatement(theSQL, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			if (theParams != null) {
				// 设置查询参数
				int size = theParams.size();
				for (int i = 0; i < size; i++) {
					theParams.get(i).fillPreparedStatement(pstmt, i + 1);
				}
			}
			// 执行查询
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result = rs.getDouble(1);
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			closeResultSet(rs);
			closeStatement(pstmt);
		}
		return result;
	}

	public static <T> T findAsValueObject(Connection theConn, String theSQL, KRowGetter<T> theRsGetter, KParameter... theParams) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		T result = null;
		try {
			pstmt = theConn.prepareStatement(theSQL, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			if (theParams != null) {
				// 设置查询参数
				for (int i = 0; i < theParams.length; i++) {
					theParams[i].fillPreparedStatement(pstmt, i + 1);
				}
			}
			// 执行查询
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result = theRsGetter.convertResultSet(rs);
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			closeResultSet(rs);
			closeStatement(pstmt);
		}
		return result;
	}

	public static <T> T findAsValueObject(Connection theConn, String theSQL, KRowGetter<T> theRsGetter, List<KParameter> theParams) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		T result = null;
		try {
			pstmt = theConn.prepareStatement(theSQL, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			if (theParams != null) {
				// 设置查询参数
				int size = theParams.size();
				for (int i = 0; i < size; i++) {
					theParams.get(i).fillPreparedStatement(pstmt, i + 1);
				}
			}
			// 执行查询
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result = theRsGetter.convertResultSet(rs);
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			closeResultSet(rs);
			closeStatement(pstmt);
		}
		return result;
	}

	public static <T> List<T> findAsList(Connection theConn, String theSQL, KRowGetter<T> theRsGetter, KParameter... theParams) throws SQLException {
		return findAsList(theConn, theSQL, theRsGetter, FETCH_SIZE, theParams);
	}

	public static <T> List<T> findAsList(Connection theConn, String theSQL, KRowGetter<T> theRsGetter, List<KParameter> theParams) throws SQLException {
		return findAsList(theConn, theSQL, theRsGetter, FETCH_SIZE, theParams);
	}

	public static <T> List<T> findAsList(Connection theConn, String theSQL, KRowGetter<T> theRsGetter, int theFetchSize, KParameter... theParams) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<T> tmpList = null;
		try {
			pstmt = theConn.prepareStatement(theSQL, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			if (theParams != null) {
				// 设置查询参数
				for (int i = 0; i < theParams.length; i++) {
					theParams[i].fillPreparedStatement(pstmt, i + 1);
				}
			}
			// 执行查询
			rs = pstmt.executeQuery();

			if (theFetchSize > 0) {
				try {
					rs.setFetchSize(theFetchSize);
				} catch (SQLException ee) {

				}
			}
			while (rs.next()) {
				if (tmpList == null) {
					tmpList = new ArrayList<T>(10);
				}
				tmpList.add(theRsGetter.convertResultSet(rs));
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			closeResultSet(rs);
			closeStatement(pstmt);
		}
		return tmpList;
	}

	public static <T> List<T> findAsList(Connection theConn, String theSQL, KRowGetter<T> theRsGetter, int theFetchSize, List<KParameter> theParams) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<T> tmpList = null;
		try {
			pstmt = theConn.prepareStatement(theSQL, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			if (theParams != null) {
				// 设置查询参数
				int size = theParams.size();
				for (int i = 0; i < size; i++) {
					theParams.get(i).fillPreparedStatement(pstmt, i + 1);
				}
			}
			// 执行查询
			rs = pstmt.executeQuery();
			if (theFetchSize > 0) {
				try {
					rs.setFetchSize(theFetchSize);
				} catch (SQLException ee) {

				}
			}
			while (rs.next()) {
				if (tmpList == null) {
					tmpList = new ArrayList<T>(10);
				}
				tmpList.add(theRsGetter.convertResultSet(rs));
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			closeResultSet(rs);
			closeStatement(pstmt);
		}
		return tmpList;
	}

	public static List<String> findAsStringList(Connection theConn, String theSQL, KParameter... theParams) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<String> tmpList = null;
		try {
			pstmt = theConn.prepareStatement(theSQL, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			if (theParams != null) {
				// 设置查询参数
				int size = theParams.length;
				for (int i = 0; i < size; i++) {
					theParams[i].fillPreparedStatement(pstmt, i + 1);
				}
			}
			// 执行查询
			rs = pstmt.executeQuery();
			try {
				rs.setFetchSize(50);
			} catch (SQLException ee) {

			}

			while (rs.next()) {
				if (tmpList == null) {
					tmpList = new ArrayList<String>(10);
				}
				tmpList.add(rs.getString(1));
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			closeResultSet(rs);
			closeStatement(pstmt);
		}
		return tmpList;
	}

	public static <T> List<T> findAsList(Connection theConn, String theQuerySQL, QueryCondition theCondition, KRowGetter<T> theRsGetter) throws SQLException {
		List<T> tmpList = null;
		if (theCondition != null && theCondition.getParameterList() != null) {
			tmpList = findAsList(theConn, theQuerySQL, theRsGetter, theCondition.getParameterList());
		} else {
			tmpList = findAsList(theConn, theQuerySQL, theRsGetter);
		}
		return tmpList;
	}

	public static <T> KPageResult<T> query(Dialect theDialect, Connection theConn, String theQuerySQL, KRowGetter<T> theRsGetter, QueryCondition theCondition) throws SQLException {
		KPageResult<T> tmp = null;
		boolean isDisplayByPage = false;
		List<KParameter> tmpParamList = null;
		if (theCondition != null) {
			isDisplayByPage = theCondition.isDisplayByPage();
			tmpParamList = theCondition.getParameterList();
		}
		if (isDisplayByPage) {
			int pageSize = theCondition.getPageSize();
			int pageNo = theCondition.getPageNo();
			KJdbcDivPageQueryer<T> tmpDivPageQueryer = new KJdbcDivPageQueryer<T>(theDialect, theConn, theQuerySQL, tmpParamList, pageNo, pageSize, theRsGetter);
			tmp = tmpDivPageQueryer.query();
		} else {
			List<T> tmpList = findAsList(theConn, theQuerySQL, theRsGetter, tmpParamList);
			if (tmpList != null) {
				tmp = new KPageResult<T>(tmpList);
			}
		}
		return tmp;
	}

	public static boolean hasDataExisted(Connection theConn, String theSQL, KParameter... theParams) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean result = false;
		try {
			pstmt = theConn.prepareStatement(theSQL);
			if (theParams != null) {
				// 设置查询参数
				for (int i = 0; i < theParams.length; i++) {
					theParams[i].fillPreparedStatement(pstmt, i + 1);
				}
			}
			// 执行查询
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result = true;
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			closeResultSet(rs);
			closeStatement(pstmt);
		}
		return result;
	}

	public static boolean hasDataExistedByCount(Connection theConn, String theSQL, KParameter... theParams) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean result = false;
		try {
			pstmt = theConn.prepareStatement(theSQL);
			if (theParams != null) {
				// 设置查询参数
				for (int i = 0; i < theParams.length; i++) {
					theParams[i].fillPreparedStatement(pstmt, i + 1);
				}
			}
			// 执行查询
			rs = pstmt.executeQuery();
			if (rs.next()) {
				if (rs.getInt(1) > 0) {
					result = true;
				}
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			closeResultSet(rs);
			closeStatement(pstmt);
		}
		return result;
	}

	public static boolean hasDataExistedByCount(Connection theConn, String theSQL, List<KParameter> theParams) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean result = false;
		try {
			pstmt = theConn.prepareStatement(theSQL, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			if (theParams != null) {
				int size = theParams.size();
				// 设置查询参数
				for (int i = 0; i < size; i++) {
					theParams.get(i).fillPreparedStatement(pstmt, i + 1);
				}
			}
			// 执行查询
			rs = pstmt.executeQuery();
			if (rs.next()) {
				if (rs.getInt(1) > 0) {
					result = true;
				}
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			closeResultSet(rs);
			closeStatement(pstmt);
		}
		return result;
	}

	public static <T> int singleExecute(Connection theConn, String theSQL, T theModel, KRowSetter<T> theStmtSetter) throws SQLException {
		int rowCount = -1;
		PreparedStatement stmt = null;
		try {
			stmt = theConn.prepareStatement(theSQL);
			theStmtSetter.fillStatement(stmt, theModel);
			rowCount = stmt.executeUpdate();
		} catch (SQLException e) {
			throw e;
		} finally {
			closeStatement(stmt);
		}
		return rowCount;
	}

	public static int singleExecute(Connection theConn, String theSQL, KParameter... theParams) throws SQLException {
		int rowCount = -1;
		PreparedStatement stmt = null;
		try {
			stmt = theConn.prepareStatement(theSQL);
			if (theParams != null) {
				// 设置查询参数
				for (int i = 0; i < theParams.length; i++) {
					theParams[i].fillPreparedStatement(stmt, i + 1);
				}
			}
			rowCount = stmt.executeUpdate();
		} catch (SQLException e) {
			throw e;
		} finally {
			closeStatement(stmt);
		}
		return rowCount;
	}

	public static int singleExecute(Connection theConn, String theSQL, List<KParameter> theParams) throws SQLException {
		int rowCount = -1;
		PreparedStatement stmt = null;
		try {
			stmt = theConn.prepareStatement(theSQL);
			if (theParams != null) {
				// 设置查询参数
				int size = theParams.size();
				for (int i = 0; i < size; i++) {
					theParams.get(i).fillPreparedStatement(stmt, i + 1);
				}
			}
			rowCount = stmt.executeUpdate();
		} catch (SQLException e) {
			throw e;
		} finally {
			closeStatement(stmt);
		}
		return rowCount;
	}

	public static <T> int[] batchExecute(Connection theConn, String theSQL, List<T> theModelList, KRowSetter<T> theStmtSetter) throws SQLException {
		PreparedStatement stmt = null;
		try {
			stmt = theConn.prepareStatement(theSQL);
			int voListSize = theModelList.size();
			for (int k = 0; k < voListSize; k++) {
				theStmtSetter.fillStatement(stmt, theModelList.get(k));
				stmt.addBatch();
			}
			return stmt.executeBatch();
		} catch (SQLException e) {
			throw e;
		} finally {
			closeStatement(stmt);
		}
	}

	public static Map<String, Double> findAsMapStringDouble(Connection theConn, String theSQL, KParameter... theParams) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Map<String, Double> result = new HashMap<String, Double>(1000);
		String key = null;
		try {
			pstmt = theConn.prepareStatement(theSQL, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			if (theParams != null) {
				// 设置查询参数
				for (int i = 0; i < theParams.length; i++) {
					theParams[i].fillPreparedStatement(pstmt, i + 1);
				}
			}
			// 执行查询
			rs = pstmt.executeQuery();
			while (rs.next()) {
				key = rs.getString(1);
				key = key == null ? "" : key.trim();
				result.put(key, rs.getDouble(2));
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			closeResultSet(rs);
			closeStatement(pstmt);
		}
		return result;
	}

	public static Map<String, String> findAsMapStringString(Connection theConn, String theSQL, KParameter... theParams) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Map<String, String> result = new HashMap<String, String>(1000);
		String key = null;
		try {
			pstmt = theConn.prepareStatement(theSQL, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			if (theParams != null) {
				// 设置查询参数
				for (int i = 0; i < theParams.length; i++) {
					theParams[i].fillPreparedStatement(pstmt, i + 1);
				}
			}
			// 执行查询
			rs = pstmt.executeQuery();
			while (rs.next()) {
				key = rs.getString(1);
				key = key == null ? "" : key.trim();
				result.put(key, rs.getString(2));
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			closeResultSet(rs);
			closeStatement(pstmt);
		}
		return result;
	}

	// ==============================================关闭相关资源========================================================
	// 关闭Statement and ResultSet
	public static void close(Statement statement, ResultSet rs) {
		closeResultSet(rs);
		closeStatement(statement);
	}

	// 关闭连接
	public static void closeConnection(Connection con) {
		try {
			if (con != null) {
				con.close();
			}
		} catch (SQLException e) {

		}
		con = null;
	}

	public static void closeStatement(Statement statement) {
		try {
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
		}
		statement = null;
	}

	public static void closeResultSet(ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
		}
		rs = null;
	}

	public static KSystemException createException(String theSQL, SQLException e) {
		String tmp = theSQL.toLowerCase().trim();
		KSystemException tmpAppSystemException = null;
		if (tmp.startsWith("insert")) {
			tmpAppSystemException = KSystemException.wrap(e, KSystemErrorCode.DAO_Insert_Exception);
		} else if (tmp.startsWith("update")) {
			tmpAppSystemException = KSystemException.wrap(e, KSystemErrorCode.DAO_Update_Exception);
		} else if (tmp.startsWith("delete")) {
			tmpAppSystemException = KSystemException.wrap(e, KSystemErrorCode.DAO_Delete_Exception);
		} else if (tmp.startsWith("select")) {
			tmpAppSystemException = KSystemException.wrap(e, KSystemErrorCode.DAO_Read_Exception);
		} else {
			tmpAppSystemException = KSystemException.wrap(e, KSystemErrorCode.DAO_Excute_Exception);
		}
		return tmpAppSystemException;
	}
}
